#-*- coding: utf-8 -*-

#needed imports
from GetData import GetData
import re
import sqlite3

#class
class GetStatistics():
    #constructor
    def __init__(self):
        self.data = GetData()
        self.connect = sqlite3.connect('rebidesDB.db')
        self.cursor = self.connect.cursor()
        self.numTotalInstitutions = 0
        self.numTotalTeachers = 0
        self.numTotalInstitutions = 0
        pass

    #get number of teacher by year
    #@param year - year to search
    #@return len(r) - number of teachers
    def get_num_teachers_year(self, year):
        x = """ SELECT year FROM teachers_cards WHERE year = {0}""".format(year)
        self.cursor.execute(x)
        r = self.cursor.fetchall()
        print "Year: 200{0} : {1} Teachers".format(year, len(r)) 
        return len(r)

    #get all the theachers in any card in aly collum
    #@param wheretosearch - collum to search
    #@param card - card were to search
    #@param year - year to search
    #@return teachersPer - list whit the numbers
    def get_num_teachers_anywhere_per_year(self, whereToSearch, card, year):
        x = """ SELECT {0} FROM {1} WHERE year = '{2}'""".format(whereToSearch, card, year)
        self.cursor.execute(x)
        r = self.cursor.fetchall()
        teachersPer = {}
        for x in r:
            if teachersPer.has_key(x[0]):
                pass  #do noting
            else:
                count = 0
                y = """ SELECT {0}, year FROM {1} WHERE {0} = '{2}'""".format(whereToSearch , card, x[0].encode('utf8'))
                self.cursor.execute(y)
                c = self.cursor.fetchall()
                for x in c:
                    if x[1] == year:
                        count += 1
                teachersPer[x[0]] = count
        for institution, numTeachers in teachersPer.iteritems():
            print "Year: 200{0} : '{1}' : {2} Teachers".format(year, institution.encode('utf8'), numTeachers)
        return teachersPer

    #get number of teachers per degree and establishment
    #@param year - year to search
    #@return teachersPerDeY - list whit the numbers
    def get_num_teachers_degree_establishment_year(self, year):
        x = """ SELECT establishments, year FROM teachers_cards WHERE year = '{0}'""".format(year)
        self.cursor.execute(x)
        r = self.cursor.fetchall()
        
        x = """ SELECT degree, year FROM curse_cards WHERE year = '{0}'""".format(year)
        self.cursor.execute(x)
        s = self.cursor.fetchall()
        
        teachersPerDeEsY = {} 
        for x in range(len(s)):
            if teachersPerDeEsY.has_key((r[x][0], s[x][0])):
                teachersPerDeEsY[(r[x][0], s[x][0])] += 1
            else:
                teachersPerDeEsY[(r[x][0], s[x][0])] = 1
        for degreeEstablishment, numTeachers in teachersPerDeEsY.iteritems():
            print "Year: 200{0} : '{1}', '{2}' : {3} Teachers".format(year, degreeEstablishment[0].encode('utf8'), degreeEstablishment[1].encode('utf8'), numTeachers)
        return teachersPerDeEsY
    
    #get all the lists
    #@param wheretosearch - collum to search
    #@param year - year to search
    #@return list - list whit the numbers
    def get_lists(self, whereToSearch, year):
        x = """ SELECT {0}, year FROM teachers_cards WHERE year = '{1}'""".format(whereToSearch, year)
        self.cursor.execute(x)
        r = self.cursor.fetchall()
        list = []
        for x in r:
            if list.count(x[0]) == 0:
                list.append(x[0])
        return list

